package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.TimeGameRegDto;
import com.dy.yunying.api.datacenter.vo.TimeGameDeviceRegVO;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.util.List;
import java.util.Objects;

/**
 * @author hma
 * @date 2022/8/19 17:02
 */
@Slf4j
@Component(value = "timeGameRegDao")
public class TimeGameRegDao {
	@Resource(name = "dorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	public List<TimeGameDeviceRegVO> list(TimeGameRegDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append(	"select  `day` , " +
				"\taccountNums,-- 新增用户\n" +
				"\t accountNums1,-- 新增用户1:00\n" +
				"\t accountNums2,-- 新增用户2:00\n" +
				"\t accountNums3,-- 新增用户3:00\n" +
				"\t accountNums4,-- 新增用户4:00\n" +
				"\taccountNums5,-- 新增用户5:00\n" +
				"\taccountNums6,-- 新增用户6:00\n" +
				"\taccountNums7,-- 新增用户7:00\n" +
				"\taccountNums8,-- 新增用户8:00\n" +
				"\taccountNums9,-- 新增用户9:00\n" +
				"\taccountNums10,-- 新增用户10:00\n" +
				"\taccountNums11,-- 新增用户11:00\n" +
				"\taccountNums12,-- 新增用户12:00\n" +
				"\taccountNums13,-- 新增用户13:00\n" +
				"\taccountNums14,-- 新增用户14:00\n" +
				"\taccountNums15,-- 新增用户15:00\n" +
				"\taccountNums16,-- 新增用户16:00\n" +
				"\t accountNums17,-- 新增用户17:00\n" +
				"\taccountNums18,-- 新增用户18:00\n" +
				"\taccountNums19,-- 新增用户19:00\n" +
				"\t accountNums20,-- 新增用户20:00\n" +
				"\t accountNums21,-- 新增用户21:00\n" +
				"\t accountNums22,-- 新增用户22:00\n" +
				"\t accountNums23,-- 新增用户23:00\n" +
				"\taccountNums24-- 新增用户24:00\n" +
				"FROM\n" +
				"(")
				.append(getBaseSql(req)).append(" ) a \n");
		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    day DESC\n");
		}

		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}
		log.info("分时游戏注册表分页查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<TimeGameDeviceRegVO> timeGameDeviceRegVOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(TimeGameDeviceRegVO.class));
		log.info("分时游戏注册查询分页耗时: {}ms", System.currentTimeMillis() - start);
		return timeGameDeviceRegVOList;
	}


	private StringBuilder getBaseSql(TimeGameRegDto req) {
		StringBuilder sql = new StringBuilder();
		String regTab=null;
		String userName=null;
		Integer dim=req.getDim();
		if(null == dim){
			dim=1;
		}
		if(1== dim){
			regTab=yunYingProperties.getDailyDataAccountRegTableData();
			userName="a.username";
		}else if(2 == dim){
			regTab=yunYingProperties.getDailyDataDeviceRegTableData();
			userName="a.uuid";
		}
		sql.append("SELECT\n" );
		if(1== req.getCycle()){
			sql.append("\t\t\t\t\ta.reg_date AS `day`,\n" );
		}else{
			sql.append("\t\t\t\t\t'汇总' AS `day`,\n" );
		}
				sql.append(" count(distinct   ").append(userName).append(" )").append("accountNums,")
				.append(" count(distinct  case when  a.hour = 0  then ").append(userName).append("  else null  end )  accountNums1 ,\n" )
				.append(" count(distinct case when a.hour = 1  then ").append(userName).append("   else null  end  )  accountNums2,\n" )
				.append(" count(distinct case when a.hour = 2  then ").append(userName).append("   else null  end  )  accountNums3,\n" )
				.append(" count(distinct case when  a.hour = 3  then ").append(userName).append("   else null  end  )  accountNums4,\n" )
				.append(" count(distinct case when  a.hour = 4 then ").append(userName).append("   else null  end  )  accountNums5,\n" )
				.append(" count(distinct case when  a.hour = 5  then ").append(userName).append("   else null  end  )  accountNums6,\n" )
				.append(" count(distinct case when a.hour = 6 then ").append(userName).append("   else null  end  )  accountNums7,\n" )
				.append(" count(distinct case when  a.hour = 7 then ").append(userName).append("   else null  end  )  accountNums8,\n" )
				.append(" count(distinct case when  a.hour = 8 then ").append(userName).append("   else null  end  )  accountNums9,\n" )
				.append(" count(distinct case when  a.hour = 9 then ").append(userName).append("   else null  end  )  accountNums10,\n" )
				.append(" count(distinct case when a.hour = 10 then ").append(userName).append("   else null  end  )  accountNums11,\n" )
				.append(" count(distinct case when  a.hour = 11 then ").append(userName).append("   else null  end  )  accountNums12,\n" )
				.append(" count(distinct case when a.hour = 12 then ").append(userName).append("   else null  end  )  accountNums13,\n" )
				.append(" count(distinct case when a.hour = 13 then ").append(userName).append("   else null  end  )  accountNums14,\n" )
				.append(" count(distinct case when  a.hour = 14 then ").append(userName).append("   else null  end  )  accountNums15,\n" )
				.append(" count(distinct case when  a.hour = 15 then ").append(userName).append("   else null  end  )  accountNums16,\n" )
				.append(" count(distinct case when  a.hour = 16 then ").append(userName).append("   else null  end  )  accountNums17,\n" )
				.append(" count(distinct case when  a.hour = 17 then ").append(userName).append("   else null  end  )  accountNums18,\n" )
				.append(" count(distinct case when  a.hour = 18 then ").append(userName).append("   else null  end  )  accountNums19,\n" )
				.append(" count(distinct case when  a.hour = 19  then ").append(userName).append("   else null  end  )  accountNums20,\n" )
				.append(" count(distinct case when  a.hour = 20  then ").append(userName).append("   else null  end  )  accountNums21,\n" )
				.append(" count(distinct case when  a.hour = 21  then ").append(userName).append("   else null  end  )  accountNums22,\n" )
				.append(" count(distinct case when  a.hour = 22  then ").append(userName).append("   else null  end  )  accountNums23,\n" )
				.append(" count(distinct case when  a.hour = 23  then ").append(userName).append("   else null  end  )  accountNums24\n" )
				.append("\t\t\t\tFROM \n")
				.append(regTab).append(" a \n")
				.append(" where ")
				.append(selectCommonCondition(req)).append("\n");
		if(1== req.getCycle()){
			sql.append("GROUP BY  a.reg_date \n" );
		}

		return sql;
	}

	public String selectCommonCondition(TimeGameRegDto req) {
		String startTime = req.getStartTime();
		String endTime = req.getEndTime();
		String pgidArr = req.getPgidArr();
		String gameidArr = req.getGameidArr();
		String parentchlArr = req.getParentchlArr();
		String chlArr = req.getChlArr();
		String appchlArr = req.getAppchlArr();
		StringBuilder commonCondition = new StringBuilder();
		commonCondition.append(" a.dx_app_code = ")
				.append(yunYingProperties.getDxAPPCode())
				.append(StringUtils.SPACE);
		if (StringUtils.isNotEmpty(pgidArr)) {
			commonCondition.append("and a.app_main in ( ").append(pgidArr).append(" )")
					.append(StringUtils.SPACE);
		}
		if (StringUtils.isNotEmpty(gameidArr)) {
			commonCondition.append("and a.app_code in ( ").append(gameidArr).append(" )")
					.append(StringUtils.SPACE);
		}
		commonCondition.append("and a.reg_date >= ").append("\'" + startTime + "\'")
				.append(StringUtils.SPACE)
				.append("and a.reg_date <= ").append("\'" + endTime + "\'")
				.append(StringUtils.SPACE);

		if (StringUtils.isNotEmpty(parentchlArr)) {

			String[] split = parentchlArr.split(",");
			if (split != null && split.length > 0) {
				commonCondition.append("and a.chl_main in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}

		}
		if (StringUtils.isNotEmpty(chlArr) ) {
			String[] split = chlArr.split(",");
			if (split != null && split.length > 0) {
				commonCondition.append("and a.chl_sub in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}
		}
		if (StringUtils.isNotEmpty(appchlArr)) {
			String[] split = appchlArr.split(",");
			if (split != null && split.length > 0) {
				commonCondition.append("and a.chl_app in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);
			}
		}
		return commonCondition.toString();
	}

	public Long count(TimeGameRegDto req) {
		req.setCycle(1);
		final StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(getBaseSql(req));
		countSql.append("    ) a\n");
		return dorisTemplate.queryForObject(countSql.toString(), Long.class);
	}

	public List<TimeGameDeviceRegVO> collect(TimeGameRegDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append(	"select '汇总'  AS day," +
				"\tsum(accountNums) AS accountNums,-- 新增用户\n" +
				"\tsum(accountNums1) AS accountNums1,-- 新增用户1:00\n" +
				"\tsum(accountNums2) AS accountNums2,-- 新增用户2:00\n" +
				"\tsum(accountNums3) AS accountNums3,-- 新增用户3:00\n" +
				"\tsum(accountNums4) AS accountNums4,-- 新增用户4:00\n" +
				"\tsum(accountNums5) AS accountNums5,-- 新增用户5:00\n" +
				"\tsum(accountNums6) AS accountNums6,-- 新增用户6:00\n" +
				"\tsum(accountNums7) AS accountNums7,-- 新增用户7:00\n" +
				"\tsum(accountNums8) AS accountNums8,-- 新增用户8:00\n" +
				"\tsum(accountNums9) AS accountNums9,-- 新增用户9:00\n" +
				"\tsum(accountNums10) AS accountNums10,-- 新增用户10:00\n" +
				"\tsum(accountNums11) AS accountNums11,-- 新增用户11:00\n" +
				"\tsum(accountNums12) AS accountNums12,-- 新增用户12:00\n" +
				"\tsum(accountNums13) AS accountNums13,-- 新增用户13:00\n" +
				"\tsum(accountNums14) AS accountNums14,-- 新增用户14:00\n" +
				"\tsum(accountNums15) AS accountNums15,-- 新增用户15:00\n" +
				"\tsum(accountNums16) AS accountNums16,-- 新增用户16:00\n" +
				"\tsum(accountNums17) AS accountNums17,-- 新增用户17:00\n" +
				"\tsum(accountNums18) AS accountNums18,-- 新增用户18:00\n" +
				"\tsum(accountNums19) AS accountNums19,-- 新增用户19:00\n" +
				"\tsum(accountNums20) AS accountNums20,-- 新增用户20:00\n" +
				"\tsum(accountNums21) AS accountNums21,-- 新增用户21:00\n" +
				"\tsum(accountNums22) AS accountNums22,-- 新增用户22:00\n" +
				"\tsum(accountNums23) AS accountNums23,-- 新增用户23:00\n" +
				"\tsum(accountNums24) AS accountNums24-- 新增用户24:00\n" +
				"FROM\n" +
				"(")
				.append(getBaseSql(req)).append(" ) a \n");
		log.info("分时游戏注册表汇总查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<TimeGameDeviceRegVO> timeGameDeviceRegVOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(TimeGameDeviceRegVO.class));
		log.info("分时游戏注册汇总查询耗时: {}ms", System.currentTimeMillis() - start);
		return timeGameDeviceRegVOList;

	}


}
